﻿﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.Common;
using System.Data.SqlClient;

namespace JinianNet.KuaiBlog.SQLServerDAL
{
    public class SqlHelper : DbHelper
    {
        private static SqlHelper instance;
        static object lockHelper = new object();
        public static SqlHelper CreateInstance()
        {
            if (instance == null)
            {
                lock (lockHelper)
                {
                    if (instance == null)
                    {
                        instance = new SqlHelper(System.Configuration.ConfigurationManager.AppSettings["dsn"]);
                    }
                }
            }

            return instance;
        }

        public SqlHelper()
            : base(System.Configuration.ConfigurationManager.ConnectionStrings["connection"].ConnectionString)
        {

        }

        public SqlHelper(string connectionString)
            : base(connectionString)
        {

        }

        protected override System.Data.Common.DbConnection CreateConnection()
        {
            return new SqlConnection();
        }

        protected override System.Data.Common.DbCommand CreateCommand()
        {
            return new SqlCommand();
        }

        protected override System.Data.Common.DbDataAdapter CreateDataAdapter()
        {
            return new SqlDataAdapter();
        }

        protected override System.Data.Common.DbParameter CreateParameter()
        {
            return new SqlParameter();
        }

        public override string GetPageSql(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize)
        {
            if (PageSize <= 0)
            {
                PageSize = 10;
            }
            if (PageIndex < 1)
                PageIndex = 1;
            StringBuilder Sql = new StringBuilder();
            if (PageIndex == 1)
            {

                Sql.Append("select top ");
                Sql.Append(PageSize);
                Sql.Append(" ");
                Sql.Append(SqlAllFields);
                Sql.Append(" from ");
                Sql.Append(SqlTablesAndWhere);
                Sql.Append(" ");
                Sql.Append(OrderFields);
            }
            else
            {
                Sql.Append("select * FROM (select ROW_NUMBER() OVER(");
                Sql.Append(OrderFields);
                Sql.Append(") as rowNumber,");
                Sql.Append(SqlAllFields);
                Sql.Append(" from ");
                Sql.Append(SqlTablesAndWhere);
                Sql.Append(") as t where rowNumber between ");
                Sql.Append(((PageIndex - 1) * PageSize) + 1);
                Sql.Append(" and ");
                Sql.Append(PageIndex * PageSize);
            }
            return Sql.ToString();
        }
    }
}